using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Team4sCMDB
{
    public class AccessDatabase
    {
        private OleDbConnection connection;

        public AccessDatabase(string pathToDatabase)
        {
            this.connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathToDatabase + ";Persist Security Info=False");
        }

        public OleDbConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }

        public DataTable executeSelectStatement(string query)
        {
            try
            {
                DataTable results = new DataTable();
                OleDbCommand command;
                command = new OleDbCommand(query, this.connection);
                connection.Open();
                OleDbDataReader reader;
                reader = command.ExecuteReader();
                DataTable schema = reader.GetSchemaTable();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    results.Columns.Add(schema.Rows[i]["ColumnName"].ToString());
                }
                int rowCount = 0;
                while (reader.Read())
                {
                    DataRow row = results.NewRow();
                    results.Rows.Add(row);
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        results.Rows[rowCount][i] = reader[i];
                    }
                    rowCount++;
                }
                connection.Close();
                return results;
            }
            catch (OleDbException e)
            {
                connection.Close();
                System.Windows.Forms.MessageBox.Show(e.Message);
                return null;
            }
        }

        public void executeNonQuery(string query) 
        {
            OleDbCommand command = new OleDbCommand(query, this.connection);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public int executeScalar(string query)
        {
            OleDbCommand command = new OleDbCommand(query, this.connection);
            connection.Open();
            command.ExecuteNonQuery();

            command.CommandText = "SELECT @@Identity";
            int returnvalue = (int)command.ExecuteScalar();
            connection.Close();

            return returnvalue;
        }

    }
}